﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{
    public class clsKhachHangDAO
    {

        /// <summary>
        /// Kết nối SQL Server
        /// </summary>
        public CSQLServer sqlServer = new CSQLServer();

        /// <summary>
        /// Lấy DS khách hàng
        /// </summary>
        public DataTable LayBang()
        {
            string sql = "sp_GetBangKhachHang";
            DataTable table = sqlServer.readData(sql);
            return table;
        }

        public DataTable LayBangKhachHangVaThe()
        {
            string sql = "sp_GetKhachHangTheHinhVaThe";
            DataTable table = sqlServer.readData(sql);
            return table;
        }
        /// <summary>
        /// Lay thong tin khach hang theo Ma Khach Hang
        /// </summary>
        /// <param name="MaKhachHang"></param>
        /// <returns></returns>
        public clsKhachHangDTO  LayThongTinKhachHang(string MaKhachHang)
        {
            clsKhachHangDTO KhachHang = null;
            string sql = "spGetInfoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = MaKhachHang;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            if (table != null && table.Rows.Count > 0)
            {
                KhachHang = ChuyenDoi(table.Rows[0]);
            }
            return KhachHang;
        }

        public bool KiemTraTonTai(string MaKhachHang)
        {
            string sql = "spGetInfoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = MaKhachHang;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            if (table != null && table.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// Lay thong tin khach hang theo Khach hang id
        /// </summary>
        /// <param name="MaKhachHang"></param>
        /// <returns></returns>
        public clsKhachHangDTO LayThongTinKhachHang(decimal KhachHangId)
        {
            clsKhachHangDTO KhachHang = null;
            string sql = "spGetInfoKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            DataTable table = sqlServer.readData(sql, ParameterColection, valueofParameter);
            if (table != null && table.Rows.Count > 0)
            {
                KhachHang = ChuyenDoi(table.Rows[0]);
            }
            return KhachHang;
        }

        private clsKhachHangDTO ChuyenDoi(DataRow row)
        {
            try
            {
                clsKhachHangDTO KhachHang = null;
                if (row != null)
                {
                    KhachHang = new clsKhachHangDTO();
                    KhachHang.KhachHangId = decimal.Parse(row["KhachHangId"].ToString());
                    KhachHang.MaKhachHang = row["MaKhachHang"] == null ? string.Empty : row["MaKhachHang"].ToString();
                    KhachHang.HoTen = row["HoTen"] == null ? string.Empty : row["HoTen"].ToString();
                    KhachHang.MaSoThue = row["MaSoThue"] == null ? string.Empty : row["MaSoThue"].ToString();
                    KhachHang.NgaySinh = row["NgaySinh"] == null || string.IsNullOrEmpty(row["NgaySinh"].ToString()) ? new DateTime() : DateTime.Parse(row["NgaySinh"].ToString());
                    KhachHang.SoDienThoai = row["SoDienThoai"] == null ? string.Empty : row["SoDienThoai"].ToString();
                    KhachHang.Email = row["Email"] == null ? string.Empty : row["Email"].ToString();
                    KhachHang.DiaChi = row["DiaChi"] == null ? string.Empty : row["DiaChi"].ToString();
                    KhachHang.SoNha = row["SoNha"] == null ? string.Empty : row["SoNha"].ToString();
                    KhachHang.DuongId = row["DuongId"] == null || string.IsNullOrEmpty(row["DuongId"].ToString()) ? null : (int?)int.Parse(row["DuongId"].ToString());
                    KhachHang.PhuongXaId = row["PhuongXaId"] == null || string.IsNullOrEmpty(row["PhuongXaId"].ToString()) ? null : (int?)int.Parse(row["PhuongXaId"].ToString());
                    KhachHang.QuanHuyenId = row["QuanHuyenId"] == null || string.IsNullOrEmpty(row["QuanHuyenId"].ToString()) ? null : (int?)int.Parse(row["QuanHuyenId"].ToString());
                    KhachHang.TinhThanhId = row["TinhThanhId"] == null || string.IsNullOrEmpty(row["TinhThanhId"].ToString()) ? null : (int?)int.Parse(row["TinhThanhId"].ToString());
                    KhachHang.LoaiKhachHangId = row["LoaiKhachHangId"] == null || string.IsNullOrEmpty(row["LoaiKhachHangId"].ToString()) ? 0 : int.Parse(row["LoaiKhachHangId"].ToString());
                    KhachHang.GhiChu = row["GhiChu"] == null ? string.Empty : row["GhiChu"].ToString();
                    KhachHang.TrangThai = row["TrangThai"] == null ? false : bool.Parse(row["TrangThai"].ToString());
                    KhachHang.NguoiTaoId = row["NguoiTaoId"] == null || string.IsNullOrEmpty(row["NguoiTaoId"].ToString()) ? 0 : int.Parse(row["NguoiTaoId"].ToString());
                    KhachHang.NgayTao = DateTime.Parse(row["NgayTao"].ToString());
                    KhachHang.NguoiChinhSuaId = row["NguoiChinhSuaId"] == null || string.IsNullOrEmpty(row["NguoiChinhSuaId"].ToString()) ? null : (int?)int.Parse(row["NguoiChinhSuaId"].ToString());
                    KhachHang.NgayChinhSua = row["NgayChinhSua"] == null || string.IsNullOrEmpty(row["NgayChinhSua"].ToString()) ? null : (DateTime?)DateTime.Parse(row["NgayChinhSua"].ToString());
                }
                return KhachHang;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public DataTable LayBangKhachHangCauLong()
        {
            string sql = "sp_GetKhachHangCauLong";
            DataTable table = sqlServer.readData(sql);
            return table;
        }

        /// <summary>
        /// Lấy Mã Khách hàng phat sinh tiep theo
        /// </summary>
        public string LayMaKhachHangMoi()
        {
            string sql = "spGetNewMaKhachHang";
            return sqlServer.readData(sql, "@MaKhachHang");
        }

        public string LayMaKhachHangCauLongMoi()
        {
            string sql = "sp_GetNewMaKhachHangCauLong";
            return sqlServer.readData(sql, "@MaKhachHang");
        }

        /// <summary>
        /// Cập nhật khách hàng
        /// </summary>
        public int Sua(clsKhachHangDTO KhachHang)
        {
            int i = -1;
            string sql = "spUpdateKhachHang";
            string[] ParameterColection = new string[16];
            Object[] valueofParameter = new Object[16];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = KhachHang.MaKhachHang;
            ParameterColection[1] = "@HoTen";
            valueofParameter[1] = KhachHang.HoTen;
            ParameterColection[2] = "@MaSoThue";
            valueofParameter[2] = KhachHang.MaSoThue;
            ParameterColection[3] = "@NgaySinh";
            valueofParameter[3] = KhachHang.NgaySinh;
            ParameterColection[4] = "@SoDienThoai";
            valueofParameter[4] = KhachHang.SoDienThoai;
            ParameterColection[5] = "@Email";
            valueofParameter[5] = KhachHang.Email;
            ParameterColection[6] = "@DiaChi";
            valueofParameter[6] = KhachHang.DiaChi;
            ParameterColection[7] = "@SoNha";
            valueofParameter[7] = KhachHang.SoNha;
            ParameterColection[8] = "@DuongId";
            valueofParameter[8] = KhachHang.DuongId;
            ParameterColection[9] = "@PhuongXaId";
            valueofParameter[9] = KhachHang.PhuongXaId;
            ParameterColection[10] = "@QuanHuyenId";
            valueofParameter[10] = KhachHang.QuanHuyenId;
            ParameterColection[11] = "@TinhThanhId";
            valueofParameter[11] = KhachHang.TinhThanhId;
            ParameterColection[12] = "@LoaiKhachHangId";
            valueofParameter[12] = KhachHang.LoaiKhachHangId;
            ParameterColection[13] = "@GhiChu";
            valueofParameter[13] = KhachHang.GhiChu;
            ParameterColection[14] = "@NguoiChinhSuaId";
            valueofParameter[14] = KhachHang.NguoiChinhSuaId;
            ParameterColection[15] = "@NgayChinhSua";
            valueofParameter[15] = KhachHang.NgayChinhSua;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Thêm khách hàng
        /// </summary>
        public int Them(clsKhachHangDTO KhachHang)
        {
            int i = -1;
            string sql = "spInsertKhachHang";
            string[] ParameterColection = new string[15];
            Object[] valueofParameter = new Object[15];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = KhachHang.MaKhachHang;
            ParameterColection[1] = "@HoTen";
            valueofParameter[1] = KhachHang.HoTen;
            ParameterColection[2] = "@MaSoThue";
            valueofParameter[2] = KhachHang.MaSoThue;
            ParameterColection[3] = "@NgaySinh";
            valueofParameter[3] = KhachHang.NgaySinh;
            ParameterColection[4] = "@SoDienThoai";
            valueofParameter[4] = KhachHang.SoDienThoai;
            ParameterColection[5] = "@Email";
            valueofParameter[5] = KhachHang.Email;
            ParameterColection[6] = "@DiaChi";
            valueofParameter[6] = KhachHang.DiaChi;
            ParameterColection[7] = "@SoNha";
            valueofParameter[7] = KhachHang.SoNha;
            ParameterColection[8] = "@DuongId";
            valueofParameter[8] = KhachHang.DuongId;
            ParameterColection[9] = "@PhuongXaId";
            valueofParameter[9] = KhachHang.PhuongXaId;
            ParameterColection[10] = "@QuanHuyenId";
            valueofParameter[10] = KhachHang.QuanHuyenId;
            ParameterColection[11] = "@TinhThanhId";
            valueofParameter[11] = KhachHang.TinhThanhId;
            ParameterColection[12] = "@LoaiKhachHangId";
            valueofParameter[12] = KhachHang.LoaiKhachHangId;
            ParameterColection[13] = "@GhiChu";
            valueofParameter[13] = KhachHang.GhiChu;
            ParameterColection[14] = "@NguoiTaoId";
            valueofParameter[14] = KhachHang.NguoiTaoId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public int ThemKhachHangCauLong(clsKhachHangDTO KhachHang)
        {
            int i = -1;
            string sql = "sp_InsertKhachHangCauLong";
            string[] ParameterColection = new string[5];
            Object[] valueofParameter = new Object[5];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = KhachHang.MaKhachHang;
            ParameterColection[1] = "@HoTen";
            valueofParameter[1] = KhachHang.HoTen;
            ParameterColection[2] = "@SoDienThoai";
            valueofParameter[2] = KhachHang.SoDienThoai;
            ParameterColection[3] = "@DiaChi";
            valueofParameter[3] = KhachHang.DiaChi;
            ParameterColection[4] = "@LoaiKhachHangId";
            valueofParameter[4] = KhachHang.LoaiKhachHangId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }
        /// <summary>
        /// Xóa khách hàng
        /// </summary>
        public int Xoa(decimal KhachHangId)
        {
            int i = -1;
            string sql = "spDeleteKhachHang";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Xoa khach hang tam thoi
        /// </summary>
        /// <param name="KhachHangId"></param>
        /// <returns></returns>
        public int XoaTamThoi(decimal KhachHangId)
        {
            int i = -1;
            string sql = "spUpdateTrangThaiKhachHang";
            string[] ParameterColection = new string[2];
            Object[] valueofParameter = new Object[2];
            ParameterColection[0] = "@KhachHangId";
            valueofParameter[0] = KhachHangId;
            ParameterColection[1] = "@TrangThai";
            valueofParameter[1] = false;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public DataTable ReportDSKhachHang(string MaKhachHang, string TenKhachHang)
        {
            string sql = "sp_ReportDSKhachHang";
            string[] ParameterColection = new string[2];
            Object[] valueofParameter = new Object[2];
            ParameterColection[0] = "@MaKhachHang";
            valueofParameter[0] = MaKhachHang;
            ParameterColection[1] = "@TenKhachHang";
            valueofParameter[1] = TenKhachHang;

            DataTable table = sqlServer.readData(sql, "sp_ReportDSKhachHang;1", ParameterColection, valueofParameter);

            return table;
        }

        /// <summary>
        /// Thêm khách hàng
        /// </summary>
        public int ThemKhuyenMai(clsKhachHangDTO KhachHang, clsKhuyenMaiDTO KhuyenMai)
        {
            int i = -1;
            string sql = "spInsertKhachHangKhuyenMai";
            string[] ParameterColection = new string[4];
            Object[] valueofParameter = new Object[4];
            ParameterColection[0] = "@KhuyenMaiId";
            valueofParameter[0] = KhuyenMai.KhuyenMaiId;
            ParameterColection[1] = "@KhachHangId";
            valueofParameter[1] = KhachHang.KhachHangId;
            ParameterColection[2] = "@MoTa";
            valueofParameter[2] = KhuyenMai.TenKhuyenMai;
            ParameterColection[3] = "@NguoiTaoId";
            valueofParameter[3] = KhachHang.NguoiTaoId;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        /// <summary>
        /// Thêm khách hàng
        /// </summary>
        public int KetThucKhuyenMai(clsKhachHangDTO KhachHang, clsKhuyenMaiDTO KhuyenMai)
        {
            int i = -1;
            string sql = "spUpdateTrangThaiKhachHangKhuyenMai";
            string[] ParameterColection = new string[5];
            Object[] valueofParameter = new Object[5];
            ParameterColection[0] = "@KhuyenMaiId";
            valueofParameter[0] = KhuyenMai.KhuyenMaiId;
            ParameterColection[1] = "@KhachHangId";
            valueofParameter[1] = KhachHang.KhachHangId;
            ParameterColection[2] = "@TrangThai";
            valueofParameter[2] = false;
            ParameterColection[3] = "@NguoiChinhSuaId";
            valueofParameter[3] = KhachHang.NguoiChinhSuaId;
            ParameterColection[4] = "@NgayChinhSua";
            valueofParameter[4] = KhachHang.NgayChinhSua;
            i = sqlServer.writeData(sql, ParameterColection, valueofParameter);
            return i;
        }

        public DataTable ThongKeSoLuongKhachHangTheoNamTheoHang(int Nam)
        {
            string sql = "spGetBangThongKePhatTrienKhachHangTheoNam";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@Nam";
            valueofParameter[0] = Nam;
            return sqlServer.readData(sql, ParameterColection, valueofParameter);
        }

        public DataTable ThongKeSoLuongKhachHangTheoNamTheoCot(int Nam)
        {
            string sql = "spGetBangThongKePhatTrienKhachHangTheoNam_TheoCot";
            string[] ParameterColection = new string[1];
            Object[] valueofParameter = new Object[1];
            ParameterColection[0] = "@Nam";
            valueofParameter[0] = Nam;
            return sqlServer.readData(sql, ParameterColection, valueofParameter);
        }
    }
}
